package com.rt.tmpt.dao.impl.templateDao;

import com.rt.tmpt.bean.A1TemplateColumn;
import com.rt.tmpt.bean.A1TemplateTable;
import com.rt.tmpt.dao.impl.TemplateUtils;
import com.rt.tmpt.dao.intf.ITemplateDao;
import com.rt.tmpt.dto.PageDto;
import com.rt.tmpt.dto.Result;
import com.rt.tmpt.service.utils.CommonDbDao;
import com.rt.tmpt.utils.Config;
import com.rt.tmpt.utils.DateTool;
import com.rt.tmpt.utils.FenAndYuan;
import com.rt.tmpt.utils.StringUtils;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.jdbc.Work;
import org.hibernate.transform.Transformers;
import org.json.JSONArray;
import org.json.JSONObject;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import java.util.*;

/**
 * Created by wxy on 2016/12/11.
 */
@Repository(value = "templateDao")
public class TemplateParent4MySqlDao implements ITemplateDao {

    private final org.slf4j.Logger logger = LoggerFactory.getLogger(TemplateParent4MySqlDao.class);

    @Autowired
    private TemplateUtils templateUtils;

    @PersistenceContext
    private EntityManager entityManager;

    @Value("${mybatis-plus.generator.db_dialect}")
    private String db_dialect;
    @Value("${mybatis-plus.generator.db_name}")
    private String db_name;

    @Resource private CommonDbDao commonDao;


    private Session getCurrentSession(){
        Session session = (Session) entityManager.getDelegate();
        return session;
    }

    /**
     * 按照tableName字段拼接sql,
     * select * from [T1 t1 left join T2 t2 on t1.t2_id=t2.id] where ....
     * @param table
     * @param pageDto
     * @return
     * @throws Exception
     */
    @Override
    public Result templateSearchByTableNameSql(A1TemplateTable table, Map<String, String[]> parameterMap, PageDto pageDto) throws Exception{

        StringBuffer sb = new StringBuffer();
        StringBuffer sb4report = new StringBuffer();
        Result resultObj = new Result();

        Session session = getCurrentSession();
        session.doWork(new Work() {
            @Override
            public void execute(Connection connection) throws SQLException {

                //String keyRandom = paramMap.get("_keyRandom")[0];
                int page = pageDto.getPage() - 1;
                int rows = pageDto.getLimit();
                String sortOrder = pageDto.getSortOrder();
                String sortColumn = pageDto.getSortColumn();
                String tableName = table.getTableName();
                String tableNameSql = table.getTableNameSql();
                //验证参数合法性，防止SQL注入
                if(!templateUtils.check(tableName,sortColumn,sortOrder,connection)){
                    logger.info("参数非法："+tableName+";"+sortColumn+";"+sortOrder);
                    throw new SQLException("参数非法："+tableName+";"+sortColumn+";"+sortOrder);
                }

                String tableAliasName = table.getTableAliasName();
                String primaryKey = tableAliasName+"."+table.getPrimaryKey();
                if(StringUtils.isNull(sortColumn)){
                    sortColumn = table.getPrimaryKey();
                }
                sortColumn = tableAliasName+"."+sortColumn;

                //查询历史数据
                String _historyId = null;

                //找到所有查询条件字段的别名
                Map<String,String> columnAliasMap = templateUtils.loadColumnTableAlias(table);
                //找到所有字段的数据类型
                Map<String,Map<String,String>> dbcolumnsProperty = templateUtils.loadDbcolumnsProperty(table, session);
                Map<String,String> columnTableMap = (Map<String,String>)templateUtils.queryRalationTableList(table).getMap().get("columnTableMap");
                Map<String,A1TemplateColumn> transferA1TemplateColumnMap = templateUtils.transferA1TemplateColumnMap(table);

                //查询条件生成
                List<Object> paramList = new ArrayList<Object>();
                Map<String,String> conditionMap = new HashMap<String,String>();
                String conditionStr =
                        templateUtils.loadConditions(parameterMap, conditionMap,columnAliasMap,paramList,dbcolumnsProperty,
                            columnTableMap,transferA1TemplateColumnMap,table);

                Map<String,Object> resultMap = new HashMap<String, Object>();
                List list = new ArrayList();;

                //按表名拼接
                List<A1TemplateColumn> columnList = table.getA1TemplateColumnList();

                //将dataResoure=5，数据字典中数据，加载到column的option中
                templateUtils.loadColumnOptionList(columnList);

                //取“列”列表
                StringBuilder sbColumn = new StringBuilder();
                StringBuilder sbColumnSum = new StringBuilder();
                templateUtils.loadColumnString(sbColumn, sbColumnSum, columnList, columnAliasMap, dbcolumnsProperty,columnTableMap);
                String sbColumnString = sbColumn.toString();
                String sbColumnSumString = sbColumnSum.toString();

				/*
				SELECT * FROM table1 limit page,rows
				*/
                sb.setLength(0);
                sb.append("select ").append(sbColumnString);
                sb.append(" from ").append(tableNameSql);
                //sb.append(" where 1=1 ");
                if(conditionMap.keySet().size()>0){
                    //sb.append(" and ");
                    sb.append(" where ");
                    sb.append(conditionStr);
                }
                /*//历史数据查询
                if(_historyId !=null){
                    sb.append(" and ");
                    sb.append(conditionStr);
                }*/
                sb.append(" order by ").append(sortColumn).append(" ").append(sortOrder);
                sb.append(" limit ").append(rows*page).append(" , ").append(rows);
                String sqlTmp = sb.toString();
                logger.info("=====>SqlQuery:"+sqlTmp);
                List<Object> paramQueryList = new ArrayList<Object>();
                paramQueryList.addAll(paramList);
                list = queryListBySearch(sqlTmp,paramQueryList,dbcolumnsProperty,table);

                //生成报表用的sql
                sb4report.setLength(0);
                sb4report.append(" select ").append(sbColumnString);
                sb4report.append(" from ").append(tableNameSql);
                if(conditionMap.keySet().size()>0){
                    sb4report.append(" where ");
                    sb4report.append(conditionStr);
                }
                String reportString = sb4report.toString();
                logger.info("=====>SqlReport:"+reportString);

                //合计
                sb.setLength(0);
                sb.append(" select count(*) from ").append(tableNameSql);
                if(conditionMap.keySet().size()>0){
                    sb.append(" where ");
                    sb.append(conditionStr);
                }
                String sqlCount = sb.toString();
                logger.info("=====>SqlCount:"+sqlCount);
                int count = queryCountBySearch(sqlCount,paramList);

                //SUM
                Map<String,String> mapSum = null;
                if(sbColumnSumString.length()>3){
                    sb.setLength(0);
                    sb.append(" select ");
                    sb.append(sbColumnSumString);
                    sb.append(" from ").append(tableNameSql);
                    if(conditionMap.keySet().size()>0){
                        sb.append(" where ");
                        sb.append(conditionStr);
                    }
                    String sqlSum = sb.toString();
                    logger.info("=====>SqlSum:"+sqlSum);
                    Object o = queryListBySearch(sqlSum,paramList,dbcolumnsProperty,table).get(0);
                    mapSum = (Map<String,String>)o;
                }

                resultMap.put("total", count);
                resultMap.put("rows", list);
                resultMap.put("reportString", reportString);
                if(mapSum!=null){
                    List listSum = new ArrayList();
                    listSum.add(mapSum);
                    resultMap.put("footer", listSum);
                }

                resultObj.setMap(resultMap);
                resultObj.success();

            }
        });

        return resultObj;
    }

    /**
     * treegrid 查询主方法
     * @param table
     * @param paramMap
     * @return
     */
    @Override
    public Result templateSearch4treegrid(A1TemplateTable table, Map<String,String[]> paramMap, String[] params) {
        Result resultObj = new Result();

        /*String tableNameSql = table.getTableNameSql();
        String tableAliasName = table.getTableAliasName();
        String Id = tableAliasName+"."+table.getPrimaryKey();
        String ParentIdColumn = tableAliasName+".parent_id";
        Session session = getCurrentSession();

        //找到所有查询条件字段的别名
        Map<String,String> columnAliasMap = templateUtils.loadColumnTableAlias(table);
        //找到所有字段的数据类型
        Map<String,Map<String,String>> dbcolumnsProperty = templateUtils.loadDbcolumnsProperty(table, session);
        Map<String,String> columnTableMap = (Map<String,String>)templateUtils.queryRalationTableList(table).getMap().get("columnTableMap");
        Map<String,A1TemplateColumn> transferA1TemplateColumnMap = templateUtils.transferA1TemplateColumnMap(table);

        //查询条件生成
        List<Object> paramList = new ArrayList<Object>();
        Map<String,String> conditionMap = new HashMap<String,String>();
        String conditionStr = templateUtils.loadConditions(paramMap, conditionMap,columnAliasMap,paramList,dbcolumnsProperty,
                columnTableMap,transferA1TemplateColumnMap,table,params);

        //按表名拼接
        List<A1TemplateColumn> columnList = table.getA1TemplateColumnList();
        //将dataResoure=5，数据字典中数据，加载到column的option中
        templateUtils.loadColumnOptionList(columnList);

        //取“列”列表
        StringBuilder sbColumn = new StringBuilder();
        StringBuilder sbColumnSum = new StringBuilder();
        templateUtils.loadColumnString(sbColumn, sbColumnSum, columnList, columnAliasMap, dbcolumnsProperty,columnTableMap);

        JSONArray resultJsonArray = new JSONArray();
        String sbColumnString = sbColumn.toString();
        int parent_id = 0;
        resultObj = queryDB4treegrid(paramList, conditionMap, conditionStr,
                sbColumnString, tableNameSql, ParentIdColumn,
                dbcolumnsProperty, table,
                parent_id, resultJsonArray);

        //resultJsonArray = (JSONArray)resultObj.getObj();

        String resultString = resultJsonArray.toString();
        logger.debug("resultString debug:"+resultString);
        logger.debug("resultString debug:"+resultString);
        resultObj.setRemark(resultString);
        resultObj.success();*/
        return resultObj;
    }

    private Result queryDB4treegrid(List<Object> paramList, Map<String,String> conditionMap, String conditionStr,
                                    String sbColumnString, String tableNameSql, String ParentIdColumn,
                                    Map<String,Map<String,String>> dbcolumnsProperty, A1TemplateTable table,
                                    int parentId, JSONArray resultJsonArray){
        Result result = new Result();
        StringBuilder sb = new StringBuilder();

        //make sql
        sb.setLength(0);
        sb.append("select ").append(sbColumnString);
        sb.append(" from ").append(tableNameSql);
        if(conditionMap.keySet().size()>0){
            sb.append(" where ");
            sb.append(conditionStr);
            sb.append(" and ");
        }else{
            sb.append(" where ");
        }
        if(parentId == 0) {
            sb.append(" (").append(ParentIdColumn).append("=").append(parentId).append(" or ").append(ParentIdColumn).append(" is null)");
        }else{
            sb.append(" ").append(ParentIdColumn).append("=").append(parentId);
        }

        //query db
        String sqlstring = sb.toString();
        logger.info("=====>sqlstring4treegrid:"+sqlstring);

        List<Object> paramQueryList = new ArrayList<Object>();
        paramQueryList.addAll(paramList);
        List<Map<String, Object>>  list = queryListBySearch(sqlstring,paramQueryList,dbcolumnsProperty,table);


        //JSONArray jsonArray = new JSONArray();
        for(Map<String, Object> recordMap : list){
            Integer currentId = (Integer) recordMap.get("id");

            JSONObject jsonObject = new JSONObject();
            Iterator<String> iterator = recordMap.keySet().iterator();
            while(iterator.hasNext()){
                String key = iterator.next();
                String val = String.valueOf(recordMap.get(key));
                if("id".equalsIgnoreCase(key)) {
                    jsonObject.put(key, val);
                }
            }

            result = queryDB4treegrid(paramList, conditionMap, conditionStr,
                    sbColumnString, tableNameSql, ParentIdColumn,
                    dbcolumnsProperty, table,
                    currentId, resultJsonArray);

            JSONArray jsonArrayChildren = (JSONArray)result.getObj();
            jsonObject.put("children", jsonArrayChildren);
            resultJsonArray.put(jsonObject);
            logger.info(resultJsonArray.toString());
        }

        //logger.info(resultJsonArray.toString());
        //result.setObj(resultJsonArray);
        return result.success();
    }

    @Override
    public List<Map<String, Object>> queryReportBySearch(String sqlstring) {
        Session session = getCurrentSession();
        SQLQuery sqlQuery = session.createSQLQuery(sqlstring);
        Query query = sqlQuery.setReadOnly(true);
        query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        return query.list();
    }

    @Override
    public List<Map<String, Object>> queryListBySearch(String sqlstring, List<Object> paramList){
        return queryListBySearch(sqlstring, paramList, null, null);
    }

    /**
     * 根据列类型，对返回的数据集加数据转换，如：datetime -> datetime_str
     * @param sqlstring
     * @param paramList
     * @param dbcolumnsProperty
     * @return
     */
    public List<Map<String, Object>> queryListBySearch(String sqlstring, List<Object> paramList, Map<String,Map<String,String>> dbcolumnsProperty, A1TemplateTable table) {
        Session session = getCurrentSession();
        SQLQuery sqlQuery = session.createSQLQuery(sqlstring);
        int i=1;
        if(paramList!=null){
            for(Object o:paramList){
                sqlQuery.setParameter(i++, o);
            }
        }
        Query query = sqlQuery.setReadOnly(true);
        query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
        List<Map<String, Object>> list = query.list();

        //对查询出的结果集做数据转换
        transferResultList(list, dbcolumnsProperty, table);
        return list;
    }

    /**
     * 对查询出的结果集做数据转换
     * @param list
     * @param dbcolumnsProperty
     * @param table
     * @return
     */
    private void transferResultList(List<Map<String, Object>> list, Map<String,Map<String,String>> dbcolumnsProperty, A1TemplateTable table){
        if(dbcolumnsProperty==null || table==null){return;}

        //查询列所属表名
        Result result1 = templateUtils.queryRalationTableList(table);
        Map<String,String> columnTableMap = (Map<String,String>)result1.getMap().get("columnTableMap");

        //取出每一列的data_tranfer
        Map<String,A1TemplateColumn> a1TemplateColumnMap = templateUtils.transferA1TemplateColumnMap(table);

        //存放转换后的数据列表
        List<Map<String,Object>> newList = new ArrayList<>();
        //for处理column map
        for (Map<String, Object> map : list) {
            //存放转换后的数据Map
            Map<String,Object> newMap = new HashMap<>();
            Set columnSet = map.keySet();
            Iterator columnIt = columnSet.iterator();
            while (columnIt.hasNext()) {
                //原数据
                String columnName = columnIt.next().toString();
                Object columnValue = map.get(columnName);
                newMap.put(columnName, columnValue);
                //取列的模板对象
                A1TemplateColumn a1TemplateColumn = a1TemplateColumnMap.get(columnName);
                //转换
                String dbPropertyMapKey = columnTableMap.get(columnName)+"_"+columnName;
                Map<String, String> dataPropertyMap = dbcolumnsProperty.get(dbPropertyMapKey);
                if (dataPropertyMap!=null ) {
                    String dataType = dataPropertyMap.get("data_type");
                    if (dataType != null) {
                        //日期类型转换,增加列如 createdate_Str
                        if (dataType.equalsIgnoreCase("datatime")
                                || dataType.equalsIgnoreCase("date")
                                || dataType.equalsIgnoreCase("timestamp")) {
                            //在做合计时，balancedate是‘合计：’，所有出错，catch处理一下
                            try {
                                String dateformat = "yyyy-MM-dd HH:mm:ss";
                                if(a1TemplateColumn!=null) {
                                    String dataTransfer = a1TemplateColumn.getDataTransfer();
                                    if("date".equalsIgnoreCase(dataTransfer)){
                                        dateformat = "yyyy-MM-dd";
                                    }
                                }
                                String columnValue_DateStr = DateTool.date2String((Date) columnValue, dateformat);
                                newMap.put(columnName, columnValue_DateStr);
                            }catch (Exception e){
                                newMap.put(columnName, columnValue);
                            }
                        }
                    }
                }

                //转换 data_transfer
                if(a1TemplateColumn!=null) {
                    String dataTransfer = a1TemplateColumn.getDataTransfer();
                    //金额转换
                    if ("fen2yuan".equalsIgnoreCase(dataTransfer)) {
                        String yuan = FenAndYuan.fen2yuanStr(columnValue);
                        newMap.put(columnName, yuan);
                    }
                }

                //数据库中存储的string是json类型，页面展示时会变成[Object,Object]
                try {
                    if(columnValue.toString().charAt(0)=='{'
                        || columnValue.toString().charAt(0)=='[') {
                        newMap.put(columnName, " "+columnValue);
                    }
                }catch (Exception e){}

                //xxx

            }
            newList.add(newMap);
        }
        //数据load到list里返回
        list.clear();
        list.addAll(newList);
    }


    @Override
    public int queryCountBySearch(String sqlstring, List<Object> paramList) {
        Session session = getCurrentSession();
        SQLQuery sqlQuery = session.createSQLQuery(sqlstring);

        int i=1;
        if(paramList!=null){
            for(Object o:paramList){
                sqlQuery.setParameter(i++, o);
            }
        }

        Query query = sqlQuery.setReadOnly(true);
        int count = Integer.parseInt(query.uniqueResult().toString());
        return count;
    }


    @Override
    public long addData(String tableName, List<Map<String,Object>> column_list) throws HibernateException, SQLException{

        Map<String,Object> dataMap = new HashMap<>();
        Session session = getCurrentSession();
        session.doWork(new Work() {
            @Override
            public void execute(Connection connection) throws SQLException {
                long id1 = commonDao.addData(tableName, column_list, connection);
                dataMap.put("id", id1);
            }
        });

        long id = Long.parseLong(dataMap.get("id").toString());
        return id;
    }

    @Override
    public long editData(String tableName, String id, List<Map<String,Object>> column_list)
            throws HibernateException, SQLException {
        Map<String,Object> dataMap = new HashMap<>();

        Session session = getCurrentSession();
        Work work = new Work() {
            @Override
            public void execute(Connection connection) throws SQLException {
                long id1 = commonDao.updateData(tableName, id, column_list, connection);
                dataMap.put("id", id1);
            }
        };
        session.doWork(work);

        long id1 = Long.parseLong(dataMap.get("id").toString());
        return id1;
    }

    /**
     * 取表列 List
     */
    @Override
    public List<Map<String, Object>> loadColumn(String tableName){
        //"select name from paydb.dbo.syscolumns where id=(select id from paydb.dbo.sysobjects where name='FinanceOrderPaySum')";
        //Select Name FROM SysColumns Where id=Object_Id('FinanceOrderPaySum')
        StringBuilder sb = new StringBuilder();
        //sb.append("select column_name as name from information_schema.columns where table_name=? and table_schema=?");
        sb.append("select column_name as name,column_comment from information_schema.columns where table_name=? and table_schema=?");
        String sqlTableName = sb.toString();
        List<Object> paramList = new ArrayList<>();
        paramList.add(tableName);
        paramList.add(db_name);
        List<Map<String, Object>> list = queryListBySearch(sqlTableName, paramList);
        return list;
    }


    /**
     * 取表列 List
     */
    @Override
    public Map<String, Object> selectTableInfo(String tableName){
        StringBuilder sb = new StringBuilder();
        sb.append("select table_name,table_comment from information_schema.tables where table_name=? and table_schema=?");
        String sqlTableName = sb.toString();
        List<Object> paramList = new ArrayList<>();
        paramList.add(tableName);
        paramList.add(db_name);
        List<Map<String, Object>> list = queryListBySearch(sqlTableName, paramList);
        return list.get(0);
    }



    /**
     * 取表主键
     * @param table
     * @return
     */
    @Override
    public String loadTablePrimaryKey(A1TemplateTable table){
        return loadTablePrimaryKeyByTablename(table.getTableName());
    }

    /**
     * 取表主键
     * @param tablename
     * @return
     */
    @Override
    public String loadTablePrimaryKeyByTablename(String tablename){
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT COLUMN_NAME as primaryKey FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME=? and table_schema=?");
        String sqlTableName = sb.toString();
        List<Object> paramList = new ArrayList<>();
        paramList.add(tablename);
        paramList.add(db_name);
        List<Map<String, Object>> list = queryListBySearch(sqlTableName, paramList);
        if(list.size() == 0){
            return "id";
        }
        String primaryKey = (String)list.get(0).get("primaryKey");
        return primaryKey;
    }


    /**
     * 通过 mist tablename 找到所有符合的表名
     * @param mistTablename = shop_*
     * @return
     */
    @Override
    public Result queryTablenamesByMistTablename(String mistTablename){
        Result resultObj  = new Result();
        if(StringUtils.isNull(mistTablename))return resultObj.success();

        //query all table list
        Result resultObj1  = queryTablenamesInDB();
        List<Map<String, Object>> list = resultObj1.getList();
        //filter
        Map<String, Object> rMap = null;
        for(Map<String, Object> map: list){
            String tableName = (String) map.get("tableName");
            String primaryKey = (String) map.get("primaryKey");
            //filter
            if(mistTablename.contains("*")) {
                String mistTablename1 = mistTablename.substring(0,mistTablename.length()-1);
                if (!tableName.startsWith(mistTablename1)) {
                    continue;
                }
            }else if(mistTablename.contains(";")) {
                String[] mistTablenameArr = mistTablename.split(";");
                List mistTablenameList = new ArrayList();
                for (int i=0; i<mistTablenameArr.length; i++) {
                    String mTableName = mistTablenameArr[i].trim();
                    mistTablenameList.add(mTableName);
                }
                if (!mistTablenameList.contains(tableName)) {
                    continue;
                }
            }else{
                if (!tableName.equals(mistTablename)) {
                    continue;
                }
            }
            rMap = new HashMap<>();
            rMap.put("tableName",tableName);
            rMap.put("primaryKey",primaryKey);
            resultObj.getList().add(rMap);
        }
        return resultObj.success();
    }

    /**
     * 查询配置的数据库里所有表名
     * @return
     */
    public Result<Map<String, Object>> queryTablenamesInDB(){
        Result resultObj = new Result();
        //sql
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT table_name FROM INFORMATION_SCHEMA.tables WHERE table_schema=?");
        String sqlTableName = sb.toString();
        //parameter
        List<Object> paramList = new ArrayList<>();
        paramList.add(db_name);
        //query
        List<Map<String, Object>> list = queryListBySearch(sqlTableName, paramList);
        //tables list
        Map<String, Object> rMap = null;
        for(Map<String, Object> map: list){
            String tableName = (String) map.get("table_name");
            String primaryKey = loadTablePrimaryKeyByTablename(tableName);
            rMap = new HashMap<>();
            rMap.put("tableName",tableName);
            rMap.put("primaryKey",primaryKey);
            resultObj.getList().add(rMap);
        }
        return resultObj.success();
    }


    @Override
    public Result executeProcedureJob(String procedure, Map<String,Object> paramMap){
        Result result = new Result();
        try{

            Session session = getCurrentSession();
            session.doWork(new Work() {
                @Override
                public void execute(Connection connection) throws SQLException {
                    CallableStatement cstmt = null;
                    String sqlProcedureString = "{call " + procedure + "(?,?)}";
                    cstmt = connection.prepareCall(sqlProcedureString);
                    cstmt.setString(1, paramMap.toString());
                    cstmt.registerOutParameter(2, Types.VARCHAR);
                    cstmt.execute();
                }
            });

            return result.success();
        }catch (Exception e) {
            e.printStackTrace();
            return result.fault(e.getLocalizedMessage());
        }



    }

    @Override
    public List<String> queryDB(String tableName,String columnName){

        Session session = getCurrentSession();
        //验证参数合法性，防止SQL注入
        if(!templateUtils.check(tableName,columnName,session)){
            //throw new SQLException("参数非法："+tableName+";"+columnName);
            logger.info("参数非法："+tableName+";"+columnName);

            return null;
        }


        List<String> resultList = new ArrayList<String>();
        String sql = "select "+columnName+" as a from "+tableName+" where deleteFlag=0";
        List<Map<String,Object>> list = queryReportBySearch(sql);
        for(Map<String,Object> map : list){
            String s = String.valueOf(map.get("a"));
            resultList.add(s);
        }
        return resultList;
    }

    @Override
    public List<Map<String,Object>> queryDB(String tableName,String columnNameLabel,String columnNameValue){

        Session session = getCurrentSession();
        //验证参数合法性，防止SQL注入
        if(!templateUtils.check(tableName,columnNameLabel,session)){
            //throw new SQLException("参数非法："+tableName+";"+columnNameLabel);
            logger.info("参数非法："+tableName+";"+columnNameLabel);
            return null;
        }
        if(!templateUtils.check(tableName,columnNameValue,session)){
            //throw new SQLException("参数非法："+tableName+";"+columnNameValue);
            logger.info("参数非法："+tableName+";"+columnNameValue);
            return null;
        }


        List<String> resultList = new ArrayList<String>();
        StringBuilder sb = new StringBuilder();
        sb.append("select ");
        sb.append(columnNameLabel).append(" as label ,");
        sb.append(columnNameValue).append(" as value ");
        sb.append("from ").append(tableName).append(" ");
        sb.append("where deleteFlag=0");
        String sql = sb.toString();
        List<Map<String,Object>> list = queryReportBySearch(sql);
        return list;
    }

    @Override
    public List<Map<String,Object>> queryDB(String targetTableName, String targetColumns, String targetPrimaryKey, int targetTableId){

        Session session = getCurrentSession();
        //验证参数合法性，防止SQL注入
        if(!templateUtils.check4multiColumns(targetTableName, targetColumns, session)){
            //throw new SQLException("参数非法："+targetTableName+";"+targetColumns);
            logger.info("参数非法："+targetTableName+";"+targetColumns);
            return null;
        }

        List<String> resultList = new ArrayList<String>();
        StringBuilder sb = new StringBuilder();
        sb.append("select ");
        sb.append(targetColumns).append(" ");
        sb.append("from ").append(targetTableName).append(" ");
        sb.append("where ").append(targetPrimaryKey).append("=?");
        String sql = sb.toString();

        List<Object> paramList = new ArrayList<>();
        paramList.add(targetTableId);
        List<Map<String,Object>> list = queryListBySearch(sql, paramList);
        return list;
    }

    @Override
    public List<Map<String,Object>> queryDBLast(String targetTableName, String targetColumns, String targetPrimaryKey){

        Session session = getCurrentSession();
        //验证参数合法性，防止SQL注入
        if(!templateUtils.check4multiColumns(targetTableName, targetColumns, session)){
            //throw new SQLException("参数非法："+targetTableName+";"+targetColumns);
            logger.info("参数非法："+targetTableName+";"+targetColumns);
            return null;
        }
        if(!templateUtils.check(targetTableName, targetPrimaryKey, session)){
            //throw new SQLException("参数非法："+targetTableName+";"+targetPrimaryKey);
            logger.info("参数非法："+targetTableName+";"+targetPrimaryKey);
            return null;
        }

        StringBuilder sb = new StringBuilder();
        sb.append("select ");
        sb.append(targetColumns).append(" ");
        sb.append("from ").append(targetTableName).append(" ");
        sb.append("order by ").append(targetPrimaryKey).append(" desc ");
        sb.append("limit 1 ");
        String sql = sb.toString();
        List<Map<String,Object>> list = queryReportBySearch(sql);
        return list;
    }

    public boolean createTableNotExist(String tableName) throws HibernateException, SQLException{

        Session session = getCurrentSession();
        session.doWork(new Work() {
            @Override
            public void execute(Connection connection) throws SQLException {
                boolean hasTable = commonDao.hasTable(connection, tableName);
                //表不存在，创建一张表
                if(!hasTable){
                    if(!templateUtils.checkTableNameLegality(tableName)){
                        throw new SQLException("参数非法："+tableName);
                    }

                    StringBuilder sqlCreateSB = new StringBuilder();
                    sqlCreateSB.append("create table ").append(tableName).append("(");
                    sqlCreateSB.append("id bigint identity(1,1) primary key,");
                    sqlCreateSB.append("createDate datetime,");
                    sqlCreateSB.append("createMan varchar(255),");
                    sqlCreateSB.append("modifyDate datetime,");
                    sqlCreateSB.append("modifyMan varchar(255),");
                    sqlCreateSB.append("deleteFlag int");
                    sqlCreateSB.append(")");
                    String sqlCreate = sqlCreateSB.toString();
                    logger.info(sqlCreate);
                    int count = commonDao.updateData(sqlCreate, null, connection);
                    logger.info("count:"+count);
                }
            }
        });

        return true;
    }


    @Override
    public boolean execSql(String sql) throws HibernateException{

        Session session = getCurrentSession();
        session.doWork(new Work() {
            @Override
            public void execute(Connection connection) throws SQLException {
                commonDao.updateData(sql, null, connection);
            }
        });

        return true;
    }

    @Override
    public boolean alterColumnNotExist(String tableName,String columnName,String column_dataType, String column_isNull) throws HibernateException, SQLException{

        Session session = getCurrentSession();
        session.doWork(new Work() {
            @Override
            public void execute(Connection connection) throws SQLException {

                boolean hasColumn = commonDao.hasColumn(connection, tableName, columnName);
                //表不存在，创建一张表
                if(!hasColumn){
                    if(!templateUtils.checkColumnNameLegality(columnName)){
                        throw new SQLException("参数非法："+columnName);
                    }
                    StringBuilder sqlCreateSB = new StringBuilder();
                    sqlCreateSB.append("alter table ").append(tableName).append(" ");
                    sqlCreateSB.append("add ").append(columnName).append(" ").append(column_dataType).append(" ").append(column_isNull);
                    String sqlCreate = sqlCreateSB.toString();
                    logger.info(sqlCreate);
                    int count = commonDao.updateData(sqlCreate, null, connection);
                }
            }
        });

        return true;
    }

    /**
     * 取列属性相关数据
     * @param table
     * @return
     * @throws Exception
     */
    @Override
    public Result queryColumnAliasAndProperty(A1TemplateTable table) throws Exception{
        Session session = (Session) entityManager.getDelegate();
        Result resultObj = new Result();
        //找到所有查询条件字段的别名
        Map<String,String> columnAliasMap = templateUtils.loadColumnTableAlias(table);
        resultObj.getMap().put("columnAliasMap",columnAliasMap);

        //找到所有字段的数据类型 {"column_name":{"data_type":"int","length":"0"},...}
        Map<String,Map<String,String>> dbcolumnsProperty = templateUtils.loadDbcolumnsProperty(table, getCurrentSession());
        resultObj.getMap().put("dbcolumnsProperty", dbcolumnsProperty);

        return resultObj;

    }

}
